package com.example.teacherservice.repository;

import com.example.teacherservice.entity.Teacher;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;
import java.util.Optional;

public interface TeacherRepository extends JpaRepository<Teacher, Long> {
    Optional<Teacher> findByUserId(Long userId);

    // 使用原生SQL查询教师教授的班级
    @Query(value = "SELECT c.id, c.class_name as className, COUNT(sc.stu_id) as studentCount, c.create_time " +
            "FROM tea_class tc " +
            "JOIN class c ON tc.class_id = c.id " +
            "LEFT JOIN stu_class sc ON c.id = sc.class_id " +
            "WHERE tc.tea_id = :teacherId AND tc.deleted = 0 AND c.deleted = 0 " +
            "GROUP BY c.id", nativeQuery = true)
    List<Map<String, Object>> findTeacherClasses(@Param("teacherId") Long teacherId);

    // 使用原生SQL查询教师教授的课程
    @Query(value = "SELECT id, cou_name as courseName, cou_cata_num as chapterCount, " +
            "purchase as studentCount, create_time " +
            "FROM course " +
            "WHERE tea_id = :teacherId AND deleted = 0", nativeQuery = true)
    List<Map<String, Object>> findTeacherCourses(@Param("teacherId") Long teacherId);

    List<Teacher> findByClassInfoId(Long classId);

    // 根据ID查询教师
    Optional<Teacher> findById(Long id);
}